package com.ruoyi.web.controller.sqlservice.controller;

import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/ajax")
public class SysServiceController {
    @Autowired
    public JdbcTemplate jdbcTemplate;
    /**
     * 通过SQL进行数据查询
     */
    @PostMapping("/sqlData")
    public JSONObject sqlData(@RequestBody JSONObject param) {
        JSONObject result = new JSONObject();
        result.put("Succ", 0);

        try {
            if(!param.containsKey("sql_name")) throw new Exception("没有设置请求SQL模板名");

            String sql_name = param.get("sql_name").toString();
            sql_name = sql_name.replace("'", "\\'");

            String sql = "select * from sys_sql where sql_name='" + sql_name + "'";
            Map<String, Object> sysSQL = jdbcTemplate.queryForMap(sql);

            if(sysSQL.get("token") != null && !sysSQL.get("token").toString().equals(param.get("sql_token")))
                throw new Exception("无权访问【" + sql_name + "】资源");

            sql = sysSQL.get("template").toString();
            Boolean isSelect = false;
            String page = null;
            String pageSize = null;
            if (sql.toUpperCase().startsWith("SELECT")) {
                isSelect = true;

                if (sql.indexOf("1 PAGE") > 0) {
                    pageSize = param.get("sql_page_size").toString();
                    if (StringUtils.isBlank(pageSize) || Integer.parseInt(pageSize) > 1000)
                        pageSize = "10";

                    page = param.get("sql_page_index").toString();
                    if (StringUtils.isBlank(page))
                        page = "1";
                }
            }

            //如果再用? 加参数列表方式[]，那样不方便查看sql语句
            for (String key : param.keySet()) {
                if(key.contains("sql_")) continue;
                sql = sql.replace("{" + key + "}", param.get(key).toString().replace("'", "\\'"));
            }

            if (isSelect) { // select语句
                if (page != null) { // 分页处理
                    result.put("page", page);

                    List<Map<String, Object>> count = jdbcTemplate.queryForList(
                            "SELECT COUNT(1) c " + sql.substring(sql.indexOf("1 PAGE") + "1 PAGE".length()));

                    result.put("total", count.get(0).get("c").toString());

                    sql = sql.replace(", 1 PAGE", "") + " LIMIT "
                            + String.valueOf(((Integer.parseInt(page) - 1) * Integer.parseInt(pageSize))) + "," + pageSize;
                } else {
                    result.put("page", "1");
                    result.put("total", "1");
                }
                List<Map<String, Object>> record = jdbcTemplate.queryForList(sql);
                result.put("records", record.size());
                result.put("rows", record);
            } else {
                result.put("affected", jdbcTemplate.update(sql));
            }
            result.put("Succ", 1);
        } catch (Exception ex) {
            result.put("Msg", ex.getMessage());
        }

        return result;
    }
}
